﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Huirui.Cavan.Domain.Seedwork;
using Huirui.Cavan.Domain.MainBoundedContext.SearchModule;
using Huirui.Cavan.Data.Seedwork;
using Huirui.Cavan.Domain.MainBoundedContext.StockModule;
using Huirui.Cavan.Domain.MainBoundedContext.ReportModule;
using Huirui.Cavan.Core.Extension;
using NHibernate;
using Spring.Data.Common;
using System.Data;

namespace Huirui.Cavan.Infrastructure.Data.MainBoundedContext.StockModule
{
    public class StockInOutRepository : Repository<StockInOut, StockInOutSearch>, IStockInOutRepository
    {
        public IList<StockInOutReport> GetStockInReport(DateTime? startDate, DateTime? endDate, string supplierCode, string purchaseOrderCode, string wareHouseCode)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT si.Code AS StockInCode, si.InOutDate, si.SupplierCode, s.Name AS SupplierName, ISNULL(p.Currency, 'RMB') AS Currency, ");
            sql.Append("ISNULL(p.CurrencyRate, 1.0) AS CurrencyRate, t.Amount,si.CheckedStatus, ISNULL(p.SupplierInvoiceNumber, '') AS SupplierInvoiceNumber, ISNULL(si.EmployeeCode, '') AS EmployeeCode, ISNULL(ua.EmployeeCode, '') AS ProducerCode, si.Remark ");
            sql.Append("FROM StockIn si WITH (NOLOCK) INNER JOIN ( ");
            sql.Append("SELECT sid1.StockInCode, SUM(sid1.Number * sid1.UnitCost) AS Amount FROM StockInDetail sid1 WITH(NOLOCK) ");
            sql.Append("GROUP BY sid1.StockInCode) AS t ON si.Code = t.StockInCode ");
            sql.Append("INNER JOIN StockInDetail sid2 WITH (NOLOCK) ON sid2.StockInCode = si.Code ");
            sql.Append("LEFT JOIN Purchase p WITH (NOLOCK) ON si.PurchaseCode = p.Code ");
            sql.Append("INNER JOIN Supplier s WITH (NOLOCK) ON s.Code = si.SupplierCode ");
            sql.Append("INNER JOIN UserAccount ua WITH (NOLOCK) ON si.EditUser = ua.ID ");
            sql.Append("WHERE  1 = 1 ");
            if (startDate.HasValue && endDate.HasValue)
            {
                sql.Append("AND  si.InOutDate >= @StartDate AND si.InOutDate <= @EndDate ");
            }
            if (supplierCode.IsNotEmpty())
            {
                sql.Append("AND si.SupplierCode = @SupplierCode ");
            }
            if (purchaseOrderCode.IsNotEmpty())
            {
                sql.Append("AND si.PurchaseOrderCode = @PurchaseOrderCode ");
            }
            if (wareHouseCode.IsNotEmpty())
            {
                sql.Append("AND sid2.WareHouseCode = @WareHouseCode ");
            }

            IDbParameters paras = this.AdoTemplate.CreateDbParameters();

            if (startDate.HasValue && endDate.HasValue)
            {
                paras.Add("StartDate", DbType.DateTime).Value = startDate.Value;
                paras.Add("EndDate", DbType.DateTime).Value = endDate.Value;
            }
            if (supplierCode.IsNotEmpty())
            {
                paras.Add("SupplierCode", DbType.String).Value = supplierCode;
            }
            if (purchaseOrderCode.IsNotEmpty())
            {
                paras.Add("PurchaseOrderCode", DbType.String).Value = purchaseOrderCode;
            }
            if (wareHouseCode.IsNotEmpty())
            {
                paras.Add("WareHouseCode", DbType.String).Value = wareHouseCode;
            }

            return this.AdoTemplate.QueryWithRowMapper<StockInOutReport>(CommandType.Text, sql.ToString(), new StockInReportMapper(), paras);
        }
    }
}
